#!/usr/bin/env python
# Author: mingzhong.huang

from sqlalchemy import create_engine
from sqlalchemy.schema import Table, MetaData
from sqlalchemy.sql import text
from setting import config
from core.logger import logger

class WelabDoris():
    """  
    Department Handler
    pydoris库的地址: https://pypi.org/project/pydoris/
    pip install pydoris
    """
    def __init__(self, host: str="", port: int=0, username: str="", password: str="", database: str=""):
        self.host = host if host else config.DORIS_HOST 
        self.port = port if port else config.DORIS_PORT
        self.username = username if username else config.DORIS_USERNAME
        self.password = password if password else config.DORIS_PASSWORD
        self.database = database if database else config.DORIS_DATABASE
        self.conn = False
        self.init_connection()
        
    
    def init_connection(self):
        try:
            # 创建Doris的SQLAlchemy引擎
            engine = create_engine(
            f'doris://{self.username}:{self.password}@{self.host}:{self.port}/{self.database}',
            pool_size=10, pool_recycle=7200, pool_timeout=30
            )
            # 创建SQLAlchemy连接
            self.conn = engine.connect()
            
        except Exception as e:
            message = f"init doris connection error"
            logger.exception(f"{message} => {e}")
            return False
        return True



    def get_datas(self, query: str):
        try:
            if not self.conn:
                logger.error("doris connect is invalid")
                return False, []
            
            result = self.conn.execute(text(query)).fetchall()
            datas = [dict(item) for item in result]
        except Exception as e:
            message = f"get doris datas error"
            logger.exception(f"{message} => {e}")
            return False, []
        logger.info(f"get datas success, query=>{query}")
        return True, datas
    
    
    def get_services_error_count(self, table:str, cst_begin_time: str, cst_end_time: str):
        """
        获取时间范围内,每个服务的错误日志数量
        """
        query = f"""
        SELECT fields_app, COUNT(*) AS error_count
        FROM {table}
        WHERE level = 'ERROR' AND logtime >= '{cst_begin_time}' AND logtime <= '{cst_end_time}'
        GROUP BY fields_app;
        """
        result, datas = self.get_datas(query)
        return result, datas
            
          
    def get_service_exclude_count(self, table:str, fields_app: str, cst_begin_time: str, cst_end_time: str, exclude_keywords: list):
        """
        获取时间范围内, 服务排除关键字后错误日志数量
        """
        where_txt = ""
        for exclude_keyword in exclude_keywords:
            where_txt += f"AND message NOT MATCH_PHRASE '{exclude_keyword}' "
            
        query = f"""
        SELECT COUNT(*) AS error_count
        FROM {table}
        WHERE fields_app = '{fields_app}' AND level = 'ERROR' AND logtime >= '{cst_begin_time}' AND logtime <= '{cst_end_time}' {where_txt};
        """
        result, datas = self.get_datas(query)
        return result, datas
          
            
    def get_service_exclude_message(self, table:str, fields_app: str, cst_begin_time: str, cst_end_time: str, exclude_keywords: list):
        """
        获取时间范围内, 服务排除关键字后错误日志随机100条
        """
        where_txt = ""
        for exclude_keyword in exclude_keywords:
            where_txt += f"AND message NOT MATCH_PHRASE '{exclude_keyword}' "
            
        query = f"""
        SELECT fields_app, message
        FROM {table}
        WHERE fields_app = '{fields_app}' AND level = 'ERROR' AND logtime >= '{cst_begin_time}' AND logtime <= '{cst_end_time}' {where_txt}
        ORDER BY RAND() LIMIT 100;
        """
        result, datas = self.get_datas(query)
        return result, datas
            
            
            
    def get_service_last_time(self, table:str):
        """
        最新的日志时间
        """
        query = f"""
        SELECT logtime
        FROM {table}
        ORDER BY logtime DESC LIMIT 1;
        """
        result, datas = self.get_datas(query)
        return result, datas
    
    
    
    def get_routine_loads(self):
        """
        获取doris同步kafka任务的状态信息
        """
        query = f"show routine load;"
        result, datas = self.get_datas(query)
        return result, datas
    
            
            
    def get_customs_count(self, table:str, fields_app: str, level: str, cst_begin_time: str, cst_end_time: str, keyword: str, ):
        """
        获取时间范围内, 获取包含关键字的日志条数
        """
        if not keyword:
            logger.error("get custom count failed, keyword is null")
            return False, []
        
        where_txt = f"AND message MATCH_PHRASE '{keyword}' "
        if fields_app:
            where_txt += f"AND fields_app = '{fields_app}' "
            
        if level:
            where_txt += f"AND level = '{level}' "
        
        query = f"""
        SELECT fields_app, COUNT(*) AS error_count
        FROM {table}
        WHERE logtime >= '{cst_begin_time}' AND logtime <= '{cst_end_time}' {where_txt}
        GROUP BY fields_app;
        """
        result, datas = self.get_datas(query)
        return result, datas
    
    
    
    def get_http_record_agg(self, table:str, domain_name: str, request:str, verb: str, cst_begin_time: str, cst_end_time: str):
        """
        获取时间范围内, 获取http请求根据返回状态码分组聚合
        """
        
        query = f"""
        SELECT 
            CASE
                WHEN response >= 200 AND response < 300 THEN '2xx'
                WHEN response >= 300 AND response < 400 THEN '3xx'
                WHEN response >= 400 AND response < 500 THEN '4xx'
                WHEN response >= 500 AND response < 600 THEN '5xx'
            END AS response_group,
            COUNT(*) AS total_count,
            AVG(req_time) AS avg_req_time
        FROM
            {table}
        WHERE
            logtime BETWEEN '{cst_begin_time}' AND '{cst_end_time}'
            AND domain_name = '{domain_name}'
            AND request MATCH_PHRASE '{request}'
            AND verb = '{verb}'
        GROUP BY 
            response_group;
        """
        result, datas = self.get_datas(query)
        return result, datas
            
        
        
        
    def get_http_all_agg(self, table:str, cst_begin_time: str, cst_end_time: str):
        """
        获取时间范围内, 获取所有http请求根据返回状态码分组聚合
        """
        
        query = f"""
        SELECT 
            CASE
                WHEN response == 404 THEN '404'
                WHEN response == 502 THEN '502'
                WHEN response == 500 THEN '500'
                WHEN response >= 100 AND response < 200 THEN '1xx'
                WHEN response >= 200 AND response < 300 THEN '2xx'
                WHEN response >= 300 AND response < 400 THEN '3xx'
                WHEN response >= 400 AND response < 500 THEN '4xx'
                WHEN response >= 500 AND response < 600 THEN '5xx'
            END AS response_group,
            COUNT(*) AS total_count
        FROM
            {table}
        WHERE
            logtime BETWEEN '{cst_begin_time}' AND '{cst_end_time}'
            AND request NOT MATCH_PHRASE 'itsgonnafail'
        GROUP BY 
            response_group;
        """
        result, datas = self.get_datas(query)
        return result, datas
            
        
        
        
def main():
    
    # query = """
    # SELECT COUNT(*) AS error_count
    # FROM qingluan_doris
    # WHERE logtime >= '2024-03-04 00:00:04' AND 
    #       logtime <= '2024-03-05 00:00:04' AND 
    #       fields_app = 'qingluan_doris' AND
    #       level = 'ERROR';
    # """
    # welab_doris = WelabDoris()
    # welab_doris.get_datas(query)
    
    
    welab_doris = WelabDoris()
    # result, datas = welab_doris.get_http_record_agg("openresty_access_log", "bkv2.wld.net", 
    #                                                 "/gateway-service/agreement/api/v1/getCreditReport", 
    #                                                 "GET", "2024-01-25 17:50:00", "2024-01-25 17:55:00")
    result, datas = welab_doris.get_http_all_agg("openresty_access_log", "2024-03-12 17:50:00", "2024-03-14 17:55:00")
    print(datas)
    

if __name__ == '__main__':
    main()